package Database;

import java.sql.*;
import java.util.Scanner;

public class MySQLController {

    // 数据库配置信息
    private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc_study?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "123456";

    // 共享数据库连接池
    private static Connection conn = null;
    private static Statement stmt = null;

    public MySQLController() {
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            // 获取数据库连接
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("尝试与数据库建立连接...");
            stmt = conn.createStatement();
            if (conn != null && stmt != null){
                System.out.println("数据库连接成功！");
            }
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
    }

    public void addWebsite(Website website){
        if(website != null){
            String sql = "INSERT INTO websites(name,url,country) " +
                    "VALUES('"+website.getName()+"','"+website.getUrl()+"','"+website.getCountry()+"')";
            try{
                int status = stmt.executeUpdate(sql);
                if (status != 0){
                    System.out.println("添加成功");
                }else {
                    System.out.println("添加失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void deleteWebsite(int id){
        if(id != -1){
            String sql = "DELETE FROM websites WHERE id="+id;
            try{
                int status = stmt.executeUpdate(sql);
                if (status != 0){
                    System.out.println("删除成功");
                }else {
                    System.out.println("删除失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else {
            System.out.println("无效的网站 未进行任何删除操作");
        }
    }

    public void updateWebsite(int id){
        if(id != -1){
            System.out.println(" 1. 修改站点名称   2. 修改站点网址");
            System.out.println(" 3. 修改站点区域");
            System.out.print("请选择修改方式:");
            Scanner sc = new Scanner(System.in);
            int choose = sc.nextInt();
            if (choose == 1){
                System.out.print("请输入新的站点名称:");
                String newName = sc.next();
                String sql = "UPDATE websites SET name='"+newName+"' WHERE id="+id;
                try{
                    int status = stmt.executeUpdate(sql);
                    if (status != 0){
                        System.out.println("修改成功");
                    }else {
                        System.out.println("修改失败");
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else if (choose == 2) {
                System.out.println("请输入新的站点网址:");
                String newUrl = sc.next();
                String sql = "UPDATE websites SET url='"+newUrl+"' WHERE id="+id;
                try{
                    int status = stmt.executeUpdate(sql);
                    if (status != 0){
                        System.out.println("修改成功");
                    }else {
                        System.out.println("修改失败");
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }else if (choose == 3) {
                System.out.println("请输入新的站点区域:");
                String newRegion = sc.next();
                String sql = "UPDATE websites SET country='"+newRegion+"' WHERE id="+id;
                try{
                    int status = stmt.executeUpdate(sql);
                    if (status != 0){
                        System.out.println("修改成功");
                    }else {
                        System.out.println("修改失败");
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }else {
                System.out.println("请输入正确的选项");
            }
        }else {
            System.out.println("无效的网站 未进行任何修改操作");
        }
    }

    // 查询网站-通过网站名-存在返回ture否则返回false
    public int selectWebsite(String name){
        try {
            String sql = "SELECT * FROM websites WHERE name LIKE '%"+name+"%'";
            ResultSet rs = stmt.executeQuery(sql);
            int count = 0;
            int foundId = -1;
            String foundName = "";
            String foundUrl = "";
            String foundCountry = "";
            while(rs.next()) {
                count++;
                if(count == 1) {
                    // 保存第一行数据
                    foundId = rs.getInt("id");
                    foundName = rs.getString("name");
                    foundUrl = rs.getString("url");
                    foundCountry = rs.getString("country");
                }
                if (count > 1) {
                    System.out.println("存在多行匹配值 再输入更详细的网站名呢？");
                    return -1;
                }
            }
            if (count == 1) {
                System.out.print("ID: " + foundId);
                System.out.print(", 站点名称: " + foundName);
                System.out.print(", 站点URL: " + foundUrl);
                System.out.print(", 归属国: " + foundCountry);
                System.out.print("\n");
                return foundId;
            }else {
                // 不存在记录
                System.out.println("未找到该网站");
                return -1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 显示所有网站
    public void selectAllWebsite(){
        try {
            String sql = "SELECT id, name, url, country FROM websites";
            ResultSet rs = stmt.executeQuery(sql);

            while(rs.next()){
                // 通过字段检索
                int id  = rs.getInt("id");
                String name = rs.getString("name");
                String url = rs.getString("url");
                String country = rs.getString("country");

                System.out.print("ID: " + id);
                System.out.print(", 站点名称: " + name);
                System.out.print(", 站点URL: " + url);
                System.out.print(", 归属国: " + country);
                System.out.print("\n");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void closeConnection(){
        if(conn != null){
            try{
                if(stmt!=null) stmt.close();
                if(conn!=null) conn.close();
                System.out.println("数据库连接成功关闭");
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }

}